SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





ALTER             VIEW dbo.V_td_EmpMaster

AS

SELECT cast(0 as bit) as choice,td_EmpMaster.*,p.ep_postnm,
  DATEDIFF(YEAR,EM_Birthday,GETDATE())  AS EmpAge,
  case when datediff(year,EM_HereDate,getdate()) = 0 then 0 
    else datediff(year,EM_HereDate,getdate())-1 + case when datepart(month,EM_HereDate) < 7 and datepart(month,getdate()) >= 7 
                                                       then 1 else 0 end
  end AS HereYears,
  B.ED_DeptNM, B.subcode as dp_subcode,C.EG_GroupNM,E.PC_ProvinceNM,a.name as areaname,
  ISNULL(PC_ProvinceNM,'')+ISNULL(EM_Address,'') as EmpAddress,TP_TPostName
FROM     td_EmpMaster 
	  LEFT JOIN td_Dept 		AS B ON(td_EmpMaster.DeptID=B.ED_DeptID)
	  LEFT JOIN td_Group		AS C ON(td_EmpMaster.EM_GroupID=C.EG_GroupID AND td_EmpMaster.DeptID=C.EG_DeptID)
	  LEFT JOIN td_ProvinceCode 	AS E ON(td_EmpMaster.EM_ProvinceNo=E.PC_ProvinceNo)
	  LEFT JOIN td_TechnicalPost	AS I ON(td_EmpMaster.EM_TechnicalPost=I.TP_TPostID)
          left join td_post		as p on td_empmaster.em_post = p.ep_postid
          left join td_area as a on td_EmpMaster.areaid=a.id













GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

